const Router = require('koa-router');
const tool = require("../../tools/tool");
const router = new Router();
const {db, QueryTypes} = require('../../models/db')
// 采购付款一览表
router.post('/pur/cgfk', async ctx => {
    try {
        let user = ctx.user;
        let tenantId = user.tenantId;
        let body = ctx.request.body
        let excel = body.excel?body.excel:0
        let formData = body.formData
        let buId = tool.xe.toNumber(body.formData.buId)
        let beginDate = formData.beginDate ? formData.beginDate : '2001-01-01'
        let endDate = formData.endDate ? formData.endDate : tool.getDay()
        let billNo = formData.billNo?formData.billNo:''
        let sql = `
           with bills as (
                select  a.id,a.tenantId, a.billDate,a.billNo,a.buId,a.transType,a.transTypeName,
                        iif(a.transType=150502,-isnull(a.totalAmount,0),isnull(a.totalAmount,0)) totalAmount,
                       iif(a.transType=150502,-isnull(a.disAmount,0),isnull(a.disAmount,0)) disAmount,
                       iif(a.transType=150502,-isnull(a.rpAmount,0),isnull(a.rpAmount,0)) rpAmount,
                        iif(a.transType=150502,-isnull(a.ysAmount,0),isnull(a.ysAmount,0)) ysAmount,
                       0 yhAmount,
                       0 leftAmount
                from invoice a where a.billState='accounted' and a.tenantId=${tenantId} and a.billType='PUR'
                                 and a.billDate between '${beginDate}' and '${endDate}' and (buid=${buId} or ${buId}=0)
                                 and (billNo like '%${billNo}%' or '${billNo}'='')
            )
            select a.*,b.name buName from (
                  select  id,tenantId,billDate,transTypeName,billNo,buId,
                          totalAmount,
                          disAmount,
                          rpAmount+ysAmount rpAmount,
                          yhAmount,
                          totalAmount-disAmount-rpAmount-ysAmount arrears from bills
                  union all
                  select billId id,tenantId,billDate,transTypeName,billNo,buId,0 totalAmount,0 disAmount,nowCheck rpAmount,yhAmount,0-nowCheck-yhAmount arrears from (
                  select b.billId,a.tenantId,a.billDate,a.buId,a.billNo,a.billState,a.billType,a.transTypeName,isnull(b.nowCheck,0) nowCheck,isnull(b.yhAmount,0) yhAmount
                        from invoice a inner join invoice_receipt b on a.tenantId=b.tenantId and a.id=b.iid and a.billState='accounted' and a.billType='PAYMENT'
                        inner join bills c on b.billId=c.id
                  ) a
              ) a inner join contact b on a.tenantId=b.tenantId and a.buId=b.id order by id ,transTypeName desc 
        `
        let ret = await db.query(sql, {type: QueryTypes.SELECT})
        let gp = tool.xe.groupBy(ret, 'id')
        let arr = []
        for(let i in gp) {
            let list = gp[i]
            let total = 0
            let totalamount = 0
            let rptotal = 0
            let disa = 0
            let yh = 0
            list.map(row => {
                arr.push(row)
                total = tool.add(total,  row.totalAmount)
                totalamount = tool.add(totalamount,  row.amount)
                rptotal = tool.add(rptotal,  row.rpAmount)
                disa = tool.add(disa,  row.disAmount)
                yh = tool.add(yh,  row.yhAmount)
            })
            arr.push({
                transTypeName: '小计',
                id: list[0].id,
                buId: list[0].buId,
                totalAmount:total,
                disAmount: disa,
                rpAmount: rptotal,
                yhAmount: yh,
                arrears: 0,
                fkl: 1
            })
        }
        for (let i = 0; i < arr.length; i++) {
            if(i>0) {
                if(arr[i].id===arr[i-1].id) {
                    if(arr[i].transTypeName === '小计') {
                        arr[i].arrears = arr[i-1].arrears
                        arr[i].fkl = tool.xe.toNumber(arr[i].totalAmount)? (tool.xe.toFixed((tool.xe.divide(tool.Np.minus(arr[i].totalAmount,arr[i].arrears), arr[i].totalAmount)*100),2)+'%') :'0.00%'
                    }
                    else {
                        arr[i].arrears = tool.add(arr[i-1].arrears,arr[i].arrears)
                    }
                }
                else {
                    continue
                }
            }
        }
        if(excel) {
            return tool.doExcelOut(ctx, arr)
        }

        return ctx.suc('', {
            total: 0,
            list: arr
        })
    }
    catch (e) {
        return ctx.err('查询失败')
    }
})





module.exports = router;